﻿<?php if (!defined('SERVER_ROOT')) exit('No direct script access allowed');

class canbo_Model extends Model {

    function __construct()
    {
        parent::__construct();
    }
    public function SELECT_CAN_BO_BY_MDV_ALL_TR($P_MA_DON_VI){
        $P_SITE_ROOT = SITE_ROOT;
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_CAN_BO_BY_MDV_ALL_TR
                                                      (:CUR,
                                                      '$P_MA_DON_VI',
                                                      '$P_SITE_ROOT'
                                                      );END;",'CUR');
    }
    public function SELECT_CAN_BO_BY_MTCM_ALL_TR($P_MA_DON_VI,$P_MA_TO_CHUYEN_MON){
        $P_SITE_ROOT = SITE_ROOT;
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_CAN_BO_BY_MTCM_ALL_TR
                                                      (:CUR,
                                                      '$P_MA_DON_VI',
                                                      $P_MA_TO_CHUYEN_MON,
                                                      '$P_SITE_ROOT'
                                                      );END;",'CUR');
    }
    public function SELECT_CAN_BO_BY_MTCM_ALL_TR2($P_MA_TO_CHUYEN_MON){
        $P_SITE_ROOT = SITE_ROOT;
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_CAN_BO_BY_MTCM_ALL_TR2
                                                      (:CUR,
                                                      $P_MA_TO_CHUYEN_MON,
                                                      '$P_SITE_ROOT'
                                                      );END;",'CUR');
    }
    public function SELECT_CAN_BO_CHUA_PHAN_CT(){
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_CAN_BO_CHUA_PHAN_CT(:CUR);END;",'CUR');
    }
    public function qry_single_canbo($MA_CAN_BO){
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_CAN_BO_DETAIL(:CUR,'$MA_CAN_BO'); END;",'CUR');
    }
    public function qry_single_tochuyenmon($P_MA_TO_CHUYEN_MON){
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_TO_CHUYEN_MON_DETAIL(:CUR,$P_MA_TO_CHUYEN_MON); END;",'CUR');
    }
    public function update_canbo()
    {
       $P_UPDATE_TYPE = get_post_var('hdn_update_type');
       $P_NGUOI_CAP_NHAT = Session::get(TIEP_DAU_NGU_SESSION.'MA_CAN_BO');
       if($P_UPDATE_TYPE=='CAP_NHAT'){
            $P_MA_CAN_BO = get_post_var('txt_MA_CAN_BO');
        } else{
            $P_MA_CAN_BO = 0;
        }
       $P_MA_SO_CAN_BO= get_post_var('txt_MA_SO_CAN_BO');
       $P_TEN_CAN_BO= get_post_var('txt_TEN_CAN_BO');
       $P_TAI_KHOAN= get_post_var('txt_TAI_KHOAN');
       $P_MAT_KHAU = md5(md5(get_post_var('txt_MAT_KHAU')));
       $P_CMND = get_post_var('txt_CMND');
       $P_NGAY_CAP = get_post_var('txt_NGAY_CAP');
       $P_NOI_CAP = get_post_var('txt_NOI_CAP');
       $P_NOI_SINH = get_post_var('txt_NOI_SINH');
       $P_NGAY_SINH = get_post_var('txt_NGAY_SINH');
       $P_GIOI_TINH = get_post_var('cbx_GIOI_TINH');
       $P_MA_QUOC_TICH = get_post_var('cbx_MA_QUOC_TICH');
       $P_MA_DAN_TOC = get_post_var('cbx_MA_DAN_TOC');
       $P_MA_TON_GIAO = get_post_var('cbx_MA_TON_GIAO');
       $P_MA_HOC_HAM = get_post_var('cbx_MA_HOC_HAM');
       $P_MA_HOC_VI = get_post_var('cbx_MA_HOC_VI');
       $P_DC_THUONG_TRU = get_post_var('txt_DC_THUONG_TRU');
       $P_DC_LIEN_LAC = get_post_var('txt_DC_LIEN_LAC');
       $P_NGAY_VAO_DOAN = get_post_var('txt_NGAY_VAO_DOAN');
       $P_NGAY_VAO_DANG = get_post_var('txt_NGAY_VAO_DANG');
       $P_NGAY_KY_HD = get_post_var('txt_NGAY_KY_HD');
       $P_NGAY_KET_THUC_HD = get_post_var('txt_NGAY_KET_THUC_HD');
       $P_DT_DI_DONG = get_post_var('txt_DT_DI_DONG');
       $P_DT_CO_DINH = get_post_var('txt_DT_CO_DINH');
       $P_EMAIL = get_post_var('txt_EMAIL');
       $P_MA_LOAI_CAN_BO = get_post_var('cbx_MA_LOAI_CAN_BO');
       $P_SO_TAI_KHOAN = get_post_var('txt_SO_TAI_KHOAN');
       $P_MA_SO_THUE = get_post_var('txt_MA_SO_THUE');
       $P_MA_QUYEN_LOAD_DAU_TIEN = '';
       $P_STT_CAN_BO = get_post_var('txt_STT_CAN_BO');
       $P_TRANG_THAI= get_post_var('cbx_TRANG_THAI');
       $stmt = $this->db->PrepareSP("BEGIN QLCB.UPDATE_CAN_BO(:P_VAL,
                                                               :P_UPDATE_TYPE,
                                                               :P_NGUOI_CAP_NHAT,
                                                               :P_MA_CAN_BO,
                                                               :P_MA_SO_CAN_BO,
                                                               :P_TEN_CAN_BO,
                                                               :P_TAI_KHOAN,
                                                               :P_MAT_KHAU,
                                                               :P_CMND,
                                                               :P_NGAY_CAP,
                                                               :P_NOI_CAP,
                                                               :P_NOI_SINH,
                                                               :P_NGAY_SINH,
                                                               :P_GIOI_TINH,
                                                               :P_MA_QUOC_TICH,
                                                               :P_MA_DAN_TOC,
                                                               :P_MA_TON_GIAO,
                                                               :P_MA_HOC_HAM,
                                                               :P_MA_HOC_VI,
                                                               :P_DC_THUONG_TRU,
                                                               :P_DC_LIEN_LAC,
                                                               :P_NGAY_VAO_DOAN,
                                                               :P_NGAY_VAO_DANG,
                                                               :P_NGAY_KY_HD,
                                                               :P_NGAY_KET_THUC_HD,
                                                               :P_DT_DI_DONG,
                                                               :P_DT_CO_DINH,
                                                               :P_EMAIL,
                                                               :P_MA_LOAI_CAN_BO,
                                                               :P_SO_TAI_KHOAN,
                                                               :P_MA_SO_THUE,
                                                               :P_MA_QUYEN_LOAD_DAU_TIEN,
                                                               :P_STT_CAN_BO,
                                                               :P_TRANG_THAI
                                                               );END;");
        $this->db->OutParameter($stmt,$P_VAL,'P_VAL');
        $this->db->InParameter($stmt,$P_UPDATE_TYPE,'P_UPDATE_TYPE');
        $this->db->InParameter($stmt,$P_NGUOI_CAP_NHAT,'P_NGUOI_CAP_NHAT');
        $this->db->InParameter($stmt,$P_MA_CAN_BO,'P_MA_CAN_BO');
        $this->db->InParameter($stmt,$P_MA_SO_CAN_BO,'P_MA_SO_CAN_BO');
        $this->db->InParameter($stmt,$P_TEN_CAN_BO,'P_TEN_CAN_BO');
        $this->db->InParameter($stmt,$P_TAI_KHOAN,'P_TAI_KHOAN');
        $this->db->InParameter($stmt,$P_MAT_KHAU,'P_MAT_KHAU');
        $this->db->InParameter($stmt,$P_CMND,'P_CMND');
        $this->db->InParameter($stmt,$P_NGAY_CAP,'P_NGAY_CAP');
        $this->db->InParameter($stmt,$P_NOI_CAP,'P_NOI_CAP');
        $this->db->InParameter($stmt,$P_NOI_SINH,'P_NOI_SINH');
        $this->db->InParameter($stmt,$P_NGAY_SINH,'P_NGAY_SINH');
        $this->db->InParameter($stmt,$P_GIOI_TINH,'P_GIOI_TINH');
        $this->db->InParameter($stmt,$P_MA_QUOC_TICH,'P_MA_QUOC_TICH');
        $this->db->InParameter($stmt,$P_MA_DAN_TOC,'P_MA_DAN_TOC');
        $this->db->InParameter($stmt,$P_MA_TON_GIAO,'P_MA_TON_GIAO');
        $this->db->InParameter($stmt,$P_MA_HOC_HAM,'P_MA_HOC_HAM');
        $this->db->InParameter($stmt,$P_MA_HOC_VI,'P_MA_HOC_VI');
        $this->db->InParameter($stmt,$P_DC_THUONG_TRU,'P_DC_THUONG_TRU');
        $this->db->InParameter($stmt,$P_DC_LIEN_LAC,'P_DC_LIEN_LAC');
        $this->db->InParameter($stmt,$P_NGAY_VAO_DOAN,'P_NGAY_VAO_DOAN');
        $this->db->InParameter($stmt,$P_NGAY_VAO_DANG,'P_NGAY_VAO_DANG');
        $this->db->InParameter($stmt,$P_NGAY_KY_HD,'P_NGAY_KY_HD');
        $this->db->InParameter($stmt,$P_NGAY_KET_THUC_HD,'P_NGAY_KET_THUC_HD');
        $this->db->InParameter($stmt,$P_DT_DI_DONG,'P_DT_DI_DONG');
        $this->db->InParameter($stmt,$P_DT_CO_DINH,'P_DT_CO_DINH');
        $this->db->InParameter($stmt,$P_EMAIL,'P_EMAIL');
        $this->db->InParameter($stmt,$P_MA_LOAI_CAN_BO,'P_MA_LOAI_CAN_BO');
        $this->db->InParameter($stmt,$P_SO_TAI_KHOAN,'P_SO_TAI_KHOAN');
        $this->db->InParameter($stmt,$P_MA_SO_THUE,'P_MA_SO_THUE');
        $this->db->InParameter($stmt,$P_MA_QUYEN_LOAD_DAU_TIEN,'P_MA_QUYEN_LOAD_DAU_TIEN');
        $this->db->InParameter($stmt,$P_STT_CAN_BO,'P_STT_CAN_BO');
        $this->db->InParameter($stmt,$P_TRANG_THAI,'P_TRANG_THAI');
        $chaythutuc = $this->db->Execute($stmt);
        //echo '<script>alert("update type: '.$P_UPDATE_TYPE.'");</script>';
        if ($chaythutuc){
          if ($P_VAL == 'THANH_CONG'){
              $this->exec_done($this->goback_url);
          }elseif($P_VAL == 'TON_TAI_TEN_DANG_NHAP'){
              $this->exec_fail($this->goback_url,'Không thể cập nhật, tên đăng nhập đã tồn tại');
              return;
          }
          elseif($P_VAL == 'TON_TAI_MA_SO_CAN_BO'){
              $this->exec_fail($this->goback_url,'Không thể cập nhật, mã số cán bộ đã tồn tại');
              return;
          }else{
               echo $this->db->ErrorMsg();
              $this->exec_fail($this->goback_url,'Không thể cập nhật');
               return;
          }
        }else{
            echo $this->db->ErrorMsg();
               $this->exec_fail($this->goback_url,'Không thể cập nhật!! Lỗi database');
            return;
        }
    }
    public function delete_canbo(){
        $P_NGUOI_XOA = Session::get(TIEP_DAU_NGU_SESSION.'MA_CAN_BO');
        $P_MA_CAN_BO = get_post_var('hdn_item_id',0);
        //echo '<script>alert("'.$P_MA_CAN_BO.'");</script>';
        $stmt = $this->db->PrepareSP("BEGIN QLCB.DELETE_CAN_BO(:P_VAL,:P_NGUOI_XOA,:P_MA_CAN_BO);END;");
        $this->db->OutParameter($stmt,$P_VAL,'P_VAL');
        $this->db->InParameter($stmt,$P_NGUOI_XOA,'P_NGUOI_XOA');
        $this->db->InParameter($stmt,$P_MA_CAN_BO,'P_MA_CAN_BO');
        $chaythutuc = $this->db->Execute($stmt);
        if ($chaythutuc){
          if ($P_VAL == 'THANH_CONG'){
              $this->exec_done($this->goback_url);
          }else{
               echo $this->db->ErrorMsg();
              $this->exec_fail($this->goback_url,'Không thể cập nhật');
               return;
          }
        }else{
            echo $this->db->ErrorMsg();
               $this->exec_fail($this->goback_url,'Không thể cập nhật! Lỗi database');
            return;
        }
     }

     public function SELECT_ALL_TO_OF_CANBO($MA_CAN_BO) {

        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_ALL_TO_OF_CANBO(:CUR,'$MA_CAN_BO'); END;", 'CUR');
    }
    public function SELECT_ALL_TO_OF_DONVI($MA_DON_VI) {
        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_ALL_TO_OF_DONVI(:CUR,'$MA_DON_VI'); END;", 'CUR');
    }
    public function insert_thuocto() {

        $P_NGUOI_CAP_NHAT = Session::get(TIEP_DAU_NGU_SESSION.'MA_CAN_BO');
        $P_MA_CAN_BO = get_post_var('hdn_item_id');
        //echo '<script>alert("'.$P_MA_CAN_BO.'");</script>';
        $P_MA_TO_CHUYEN_MON = get_post_var('cbx_MA_TO_CHUYEN_MON');
        $P_MA_CHUC_VU = get_post_var('cbx_MA_CHUC_VU');
        $P_KIEM_NHIEM = 0;
        $P_TU_NGAY = get_post_var('txt_begin_date');
        $stmt = $this->db->PrepareSP("BEGIN QLCB.INSERT_THUOC_TO(:P_VAL,
                                                                 :P_NGUOI_CAP_NHAT,
                                                                 :P_MA_CAN_BO,
                                                                 :P_MA_TO_CHUYEN_MON,
                                                                 :P_MA_CHUC_VU,
                                                                 :P_KIEM_NHIEM,
                                                                 :P_TU_NGAY);END;");
        $this->db->OutParameter($stmt, $P_VAL, 'P_VAL');
        $this->db->InParameter($stmt, $P_NGUOI_CAP_NHAT, 'P_NGUOI_CAP_NHAT');
        $this->db->InParameter($stmt, $P_MA_CAN_BO, 'P_MA_CAN_BO');
        $this->db->InParameter($stmt, $P_MA_TO_CHUYEN_MON, 'P_MA_TO_CHUYEN_MON');
        $this->db->InParameter($stmt, $P_MA_CHUC_VU, 'P_MA_CHUC_VU');
        $this->db->InParameter($stmt, $P_KIEM_NHIEM, 'P_KIEM_NHIEM');
        $this->db->InParameter($stmt, $P_TU_NGAY, 'P_TU_NGAY');
        $chaythutuc = $this->db->Execute($stmt);
        if ($chaythutuc) {
            if ($P_VAL == 'THANH_CONG') {
                echo "<script>alert('Thành công!');</script>";
                $this->popup_exec_done();
            } elseif ($P_VAL == 'TRUNG_PHAN_TO') {
                $this->popup_exec_fail(__('Thông tin bị trùng lặp, vui lòng kiểm tra lại!'));
                return;
            } elseif ($P_VAL == 'TON_TAI_TEN') {
                $this->popup_exec_fail(__('Tên cán bộ đã tồn tại!'));
                return;
            } else {
                $this->popup_exec_fail(__('Không thể cập nhật!'));
                return;
            }
        }else{
            echo $this->db->ErrorMsg();
            $this->popup_exec_fail(__('Lỗi DB!'));
            return;
        }
        $this->popup_exec_done();
    }
    public function SELECT_THUOC_TO_DETAIL($P_MA_CAN_BO,$P_MA_TO_CHUYEN_MON,$P_MA_CHUC_VU,$P_TU_NGAY) {

        return $this->db->ExecuteCursor("BEGIN QLCB.SELECT_THUOC_TO_DETAIL(:CUR,
                                                                           $P_MA_CAN_BO,
                                                                           $P_MA_TO_CHUYEN_MON,
                                                                           '$P_MA_CHUC_VU',
                                                                           '$P_TU_NGAY'); END;", 'CUR');
    }
    public function update_thuocto() {

        $P_NGUOI_CAP_NHAT = Session::get(TIEP_DAU_NGU_SESSION.'MA_CAN_BO');
        $P_MA_CAN_BO = get_post_var('hdn_item_id_canbo');
        //echo '<script>alert("'.$P_MA_CAN_BO.'");</script>';
        $P_MA_TO_CHUYEN_MON = get_post_var('hdn_item_id_to');
        $P_MA_CHUC_VU = get_post_var('hdn_item_id_chucvu');
        $P_KIEM_NHIEM = get_post_var('chkkiemnhiem');
        if ($P_KIEM_NHIEM == 'on'){
            $P_KIEM_NHIEM = 1;
        }else{
            $P_KIEM_NHIEM = 0;
        }

        $P_TU_NGAY = get_post_var('hdn_item_id_tungay');
        $P_DEN_NGAY = get_post_var('txt_end_date');
        $stmt = $this->db->PrepareSP("BEGIN QLCB.UPDATE_THUOC_TO(:P_VAL,
                                                                 :P_NGUOI_CAP_NHAT,
                                                                 :P_MA_CAN_BO,
                                                                 :P_MA_TO_CHUYEN_MON,
                                                                 :P_MA_CHUC_VU,
                                                                 :P_KIEM_NHIEM,
                                                                 :P_TU_NGAY,
                                                                 :P_DEN_NGAY);END;");
        $this->db->OutParameter($stmt, $P_VAL, 'P_VAL');
        $this->db->InParameter($stmt, $P_NGUOI_CAP_NHAT, 'P_NGUOI_CAP_NHAT');
        $this->db->InParameter($stmt, $P_MA_CAN_BO, 'P_MA_CAN_BO');
        $this->db->InParameter($stmt, $P_MA_TO_CHUYEN_MON, 'P_MA_TO_CHUYEN_MON');
        $this->db->InParameter($stmt, $P_MA_CHUC_VU, 'P_MA_CHUC_VU');
        $this->db->InParameter($stmt, $P_KIEM_NHIEM, 'P_KIEM_NHIEM');
        $this->db->InParameter($stmt, $P_TU_NGAY, 'P_TU_NGAY');
        $this->db->InParameter($stmt, $P_DEN_NGAY, 'P_DEN_NGAY');
        $chaythutuc = $this->db->Execute($stmt);
        if ($chaythutuc) {
            if ($P_VAL == 'THANH_CONG') {
                echo "<script>alert('Thành công!');</script>";
                $this->popup_exec_done();
            } else {
                $this->popup_exec_fail(__('Không thể cập nhật!'));
                return;
            }
        }else{
            echo $this->db->ErrorMsg();
            $this->popup_exec_fail(__('Lỗi DB!'));
            return;
        }
        $this->popup_exec_done();
    }
    /****************************form dsp_all_nhomquyen***********************/
    public function SELECT_ALL_NHOM_QUYEN_CAN_BO($MA_CAN_BO){
        return $this->db->ExecuteCursor("BEGIN PHANQUYEN.SELECT_ALL_NHOM_QUYEN_CAN_BO(:CUR,$MA_CAN_BO); END;",'CUR');
    }
    public function update_nhomquyencanbo(){
        $P_NGUOI_CAP_NHAT = Session::get(TIEP_DAU_NGU_SESSION.'MA_CAN_BO');
        $P_MA_CAN_BO  = get_post_var('hdn_item_id',0);
        $stmt = $this->db->PrepareSP("BEGIN PHANQUYEN.DELETE_ALL_CB_CO_NHOM_QUYEN(:P_VAL,:P_MA_CAN_BO);END;");
        $this->db->OutParameter($stmt,$P_VAL,'P_VAL');
        $this->db->InParameter($stmt,$P_MA_CAN_BO,'P_MA_CAN_BO');
        $chaythutuc = $this->db->Execute($stmt);
        // echo '<script>alert("'.$P_VAL.'");</script>';
        if ($chaythutuc){
          if ($P_VAL == 'THANH_CONG'){
              $v_phan_nhomquyen       = get_post_var('hdn_phan_nhomquyen','');
              $arr_phan_nhomquyen = explode(',', $v_phan_nhomquyen);
              foreach ($arr_phan_nhomquyen as $P_MA_NHOM_QUYEN){
                  if ( $P_MA_NHOM_QUYEN != NULL && $P_MA_NHOM_QUYEN != ''){
                      $stmt = $this->db->PrepareSP("BEGIN PHANQUYEN.INSERT_CB_CO_NHOM_QUYEN(:P_VAL,:P_NGUOI_CAP_NHAT,:P_MA_CAN_BO,:P_MA_NHOM_QUYEN);END;");
                      $this->db->OutParameter($stmt,$P_VAL,'P_VAL');
                      $this->db->InParameter($stmt,$P_NGUOI_CAP_NHAT,'P_NGUOI_CAP_NHAT');
                      $this->db->InParameter($stmt,$P_MA_CAN_BO,'P_MA_CAN_BO');
                      $this->db->InParameter($stmt,$P_MA_NHOM_QUYEN,'P_MA_NHOM_QUYEN');
                      $chaythutuc = $this->db->Execute($stmt);
                      if($P_VAL != 'THANH_CONG'){
                           $this->popup_exec_fail(__('Không cập nhật, vui lòng liên hệ VNPT Tiền Giang: 0949291288!'));
                           return;
                      }
                  }
              }
               $this->popup_exec_done();

          }else{
                echo $this->db->ErrorMsg();
                $this->popup_exec_fail(__('Không thể cập nhật! Nhập lại dữ liệu hoặc liên hệ VNPT Tiền Giang(Pha:0949291288)'));
                return;
           }
        }

     }
}